﻿using System.Web.Services;
using System.ComponentModel;
//using Microsoft.Web.Services3;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
namespace WSEAspnetConfig
{
    /// <summary>
    /// Summary description for WSUser
    /// </summary>
    [WebService(Namespace = "WSEAspnetConfig")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    //[Policy("ServerPolicy")]
    [ToolboxItem(false)]
    public partial class Service : System.Web.Services.WebService
    {
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

        [WebMethod]
        public DataSet dsQuery(string sqlQuery)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(WSEAspnetConfig.Properties.Settings.Default.ConnectionString);
            Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            string[] lines = regex.Split(sqlQuery);
            retVal = new DataSet();

            foreach (string line in lines)
            {
                SqlCommand dbCmd = new SqlCommand(line, dbConn);
                dbCmd.CommandType = CommandType.Text;
                dbCmd.CommandTimeout = 500;
                SqlDataAdapter da = null;

                //SqlTransaction transaction =null;     //khai báo một transaction

                try
                {
                    dbConn.Open();
                    //transaction = dbConn.BeginTransaction();    //bắt đầu quá trình quản lý transaction
                    //dbCmd.Transaction = transaction;

                    da = new SqlDataAdapter(dbCmd);
                    da.Fill(retVal);


                    //transaction.Commit();      //cam kết thực hiện thành công
                }
                //catch (Exception e)    //nếu xảy ra lỗi
                //{
                //    //hiển thị thông báo lỗi tại đây

                //    //transaction.Rollback();     //quay lùi
                //}
                finally
                {
                    //transaction.Dispose();
                    da.Dispose();
                    retVal.Dispose();
                    dbConn.Dispose();
                }
            }
            return retVal;
        }
    }
}